/*
 * This class is used to manage the presistence of eSurvey
 */

package com.esurvey.core;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

public class eSurveyPersistence {
	
	//Atributes
	private static Connection conn = null;
	
	
	//Methods
	private static void connect() throws Exception {
		Properties dbProps = new Properties();
		InputStream s = eSurveyUtilities.getResource("db.properties");
		dbProps.load(s);
		String DB_NAME = dbProps.getProperty("db.dbname");
		String DB_USER = dbProps.getProperty("db.user");
		String DB_PASSWORD = dbProps.getProperty("db.password");
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		conn = DriverManager.getConnection("jdbc:mysql://localhost/" + DB_NAME + "?user=" + DB_USER + "&password="+ DB_PASSWORD);
	}
	
	private static void disconnect() throws SQLException {
		if (conn != null) conn.close();
	}
	
	private static int getDbId(PreparedStatement ps) throws SQLException {
		ResultSet ids = ps.getGeneratedKeys();
		ids.next();
		return ids.getInt(1);
	}
	
	public static void addUser(User u) throws Exception {
		// Used to save an user to the DB
		connect();
		final String INSERT_SQL = "INSERT INTO users (name, surname, login, password, email, emailVerified, secretQuestion, secretAnswer, admin) values (?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(INSERT_SQL,Statement.RETURN_GENERATED_KEYS);
		boolean lastAutoCommitState = conn.getAutoCommit();
		conn.setAutoCommit(false);
		try {
			ps.setString(1, u.getName());
			ps.setString(2, u.getSurname());
			ps.setString(3, u.getLogin());
			ps.setString(4, u.getPassword());
			ps.setString(5, u.getEmail());
			ps.setBoolean(6, u.getEmailVerified());
			ps.setString(7, u.getSecretQuestion());
			ps.setString(8, u.getSecretAnswer());
			ps.setBoolean(9, u.isAdmin());
			ps.executeUpdate();
			u.setId(getDbId(ps));
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
		conn.setAutoCommit(lastAutoCommitState);
		}
		disconnect();
	}
	
	private static ResultSet getQuery(String QUERY) throws SQLException {
		//Returns a ResultSet with the SELECT query
		Statement st = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
		st.setFetchSize(Integer.MIN_VALUE);
		ResultSet rs = st.executeQuery(QUERY);
		return rs;
	}
	
	public static boolean userExists(String login) throws Exception{
		//Checks if a login is present on DB
		boolean ok = false;
		connect();
		ResultSet rs = getQuery("SELECT * FROM users WHERE login='"+login+"'");
		ok = rs.next();
		disconnect();
		return ok;
	}
	
	private static boolean passwordOK(String login, String password) throws Exception{
		//Checks if a login and password are equals on the DB
		boolean ok=false;
		connect();
		ResultSet rs = getQuery("SELECT * FROM users WHERE login='"+login+"'");
		rs.next();
		ok = password.equals(rs.getString("password"));
		disconnect();
		return ok;
	}
	
	private static String getDBPassword(String p) {
		//Gets the password on DB format
		//TODO: Define an encription method for passowrds
		String dbpass = p;
		return dbpass;
	}
	
	public static boolean loginOK(String login, String password) throws Exception {
		//Checks if user/pass is OK, NOT if email is verified
		boolean ok=false;
		String dbpassword = getDBPassword(password);
		ok = userExists(login) && passwordOK(login, dbpassword);
		return ok;
	}
	
	public static void validateAccount(String user) throws Exception {
		//Validates an account
		connect();
		String SENTENCE="UPDATE users SET emailVerified=true WHERE login='"+user+"'";
		conn.createStatement().executeUpdate(SENTENCE);
		disconnect();
	}
	
	public static boolean emailVerified(String login) throws Exception {
		boolean ok=false;
		connect();
		ResultSet rs = getQuery("SELECT * FROM users WHERE login='"+login+"'");
		rs.next();
		ok=rs.getBoolean("emailVerified");
		disconnect();
		return ok;
	}
	
	public static boolean canLogin(String login, String password) throws Exception {
		//Checks if an user can login
		boolean ok=false;
		ok=loginOK(login,password)&&emailVerified(login);
		return ok;
	}

	public static String rescureSecretQuestion(String login) throws Exception {
		//Load and return the secret question of a user
		String secretQuestion = "";
		connect();
		String QUERY = "SELECT secretQuestion FROM users WHERE login='"+login+"'";
		ResultSet rs = getQuery(QUERY);
		
		if (rs.next() ) {
			secretQuestion = rs.getString("secretQuestion");
		}
		return secretQuestion;
	}
	
	public static void changePassword(User u) throws Exception{
		//Sets a new password
		connect();
		String SENTENCE="UPDATE users SET password='"+u.getPassword()+"' WHERE login='"+u.getLogin()+"'";
		conn.createStatement().executeUpdate(SENTENCE);
		disconnect();
	}

	public static void rescurePassword(User u) throws Exception{
		//Sets a new password
		connect();
		String SENTENCE="UPDATE users SET password='"+u.getPassword()+"', emailVerified=0 WHERE login='"+u.getLogin()+"'";
		conn.createStatement().executeUpdate(SENTENCE);
		disconnect();
	}
	
	public static User loadUser(String login) throws Exception {
		//Loads an user and it's returned as User object
		connect();
		String QUERY = "SELECT * FROM users WHERE login='"+login+"'";
		ResultSet rs = getQuery(QUERY);
		rs.next();
		int id = rs.getInt("id");
		String name = rs.getString("name");
		String surname = rs.getString("surname");
		String password = rs.getString("password");
		String email = rs.getString("email");
		boolean emailVerified = rs.getBoolean("emailVerified");
		String secretQuestion = rs.getString("secretQuestion");
		String secretAnswer = rs.getString("secretAnswer");
		boolean admin = rs.getBoolean("admin");
		disconnect();
		User u = new User(id,name,surname,login,password,email,emailVerified,secretQuestion,secretAnswer,admin);;
		return u;
	}
	
	public static User loadUser(int id) throws Exception {
		//Loads an user and it's returned as User object
		connect();
		String QUERY = "SELECT * FROM users WHERE id='"+id+"'";
		ResultSet rs = getQuery(QUERY);
		rs.next();
		String name = rs.getString("name");
		String surname = rs.getString("surname");
		String login = rs.getString("login");
		String password = rs.getString("password");
		String email = rs.getString("email");
		boolean emailVerified = rs.getBoolean("emailVerified");
		String secretQuestion = rs.getString("secretQuestion");
		String secretAnswer = rs.getString("secretAnswer");
		boolean admin = rs.getBoolean("admin");
		disconnect();
		User u = new User(id,name,surname,login,password,email,emailVerified,secretQuestion,secretAnswer,admin);
		return u;
	}
	
	public static ArrayList<Category> loadCategories() throws Exception {
		//Loads the existent categories and their's returned as String array
		ArrayList<Category> categories = new ArrayList<Category>();
		connect();
		String QUERY = "SELECT * FROM categories";
		ResultSet rs = getQuery(QUERY);	
		while (rs.next()) {
			categories.add(new Category(rs.getInt("id"), rs.getString("name")));
		}
		disconnect();
		return categories;
	}
	
	public static int addSurvey(Survey s) throws Exception {
		// Used to save an user to the DB
		connect();
		final String INSERT_SQL_SURVEYS = "INSERT INTO surveys(idOwner, subject, idCategory, creationDate, dueDate, kind, anonymous) values (?,?,?,?,?,?,?)";
		PreparedStatement psSurveys = conn.prepareStatement(INSERT_SQL_SURVEYS,Statement.RETURN_GENERATED_KEYS);
		boolean lastAutoCommitState = conn.getAutoCommit();
		conn.setAutoCommit(false);
		try {
			psSurveys.setInt(1, s.getIdOwner());
			psSurveys.setString(2, s.getSubject());
			psSurveys.setInt(3, s.getIdCategory());
			psSurveys.setDate(4, s.getCreationDate());
			psSurveys.setDate(5, s.getDueDate());
			psSurveys.setString(6, s.getKind().toString());
			psSurveys.setBoolean(7, s.isAnonymous());
			psSurveys.executeUpdate();
			s.setId(getDbId(psSurveys));
			
			for(Question q: s.getQuestions()) {
				final String INSERT_SQL_QUESTIONS = "INSERT INTO questions(idSurvey, question, kind) values (?, ?, ?)";
				PreparedStatement psQuestions = conn.prepareStatement(INSERT_SQL_QUESTIONS,Statement.RETURN_GENERATED_KEYS);
				psQuestions.setInt(1, s.getId());
				psQuestions.setString(2, q.getQuestion());
				psQuestions.setString(3, q.getKind().toString());
				psQuestions.executeUpdate();
				q.setId(getDbId(psQuestions));
				
				if (q.getAnswers() == null) {
					continue;
				}
				for (Answer a: q.getAnswers()) {
					final String INSERT_SQL_ANSWERS = "INSERT INTO answers(idQuestion, answer) values (?, ?)";
					PreparedStatement psAnswers = conn.prepareStatement(INSERT_SQL_ANSWERS,Statement.RETURN_GENERATED_KEYS);
					psAnswers.setInt(1, q.getId());
					psAnswers.setString(2, a.getAnswer());
					psAnswers.executeUpdate();
					a.setId(getDbId(psAnswers));
				}
			}
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
		conn.setAutoCommit(lastAutoCommitState);
		}
		disconnect();
		return s.getId();
	}
	
	public static Category loadCategory(String name) throws Exception {
		//Loads an category and it's returned as Category object
		connect();
		String QUERY = "SELECT * FROM categories WHERE name='"+name+"'";
		ResultSet rs = getQuery(QUERY);
		rs.next();
		int id = rs.getInt("id");
		disconnect();
		Category c = new Category(id, name);
		return c;
	}
	
	public static Category loadCategory(int id) throws Exception {
		//Loads an category and it's returned as Category object
		connect();
		String QUERY = "SELECT * FROM categories WHERE id='"+id+"'";
		ResultSet rs = getQuery(QUERY);
		rs.next();
		String name = rs.getString("name");
		disconnect();
		Category c = new Category(id, name);
		return c;
	}
	
	public static boolean emailExists(String email) throws Exception{
		//Checks if an email is present on DB
		boolean ok = false;
		connect();
		ResultSet rs = getQuery("SELECT * FROM users WHERE email='"+email+"'");
		ok = rs.next();
		disconnect();
		return ok;
	}
	
	public static ArrayList<Survey> loadSurveys(int idOwner) throws Exception {
		//Loads the existent surveys and their's returned as surveys ArrayList
		ArrayList<Survey> surveys = new ArrayList<Survey>();
		connect();
		String QUERY = "SELECT * FROM surveys where idOwner="+idOwner+"";
		ResultSet rs = getQuery(QUERY);	
		while (rs.next()) {
			int id = rs.getInt("id");
			String subject = rs.getString("subject");
			int idCategory = rs.getInt("idCategory");
			Date creationDate = rs.getDate("creationDate");
			Date dueDate = rs.getDate("dueDate");
			String kind = rs.getString("kind");
			boolean anonymous = rs.getBoolean("anonymous");
			
			surveys.add(new Survey(id, idOwner, subject, idCategory, creationDate, dueDate, kind, anonymous, null));
		}
		disconnect();
		
		connect();
		//Load the existent questions for each survey
		for (Survey s: surveys) {
			ArrayList<Question> questions = new ArrayList<Question>();
			connect();
			String QUERY_QUESTIONS = "SELECT * FROM questions where idSurvey="+s.getId()+"";
			rs = getQuery(QUERY_QUESTIONS);	
			while (rs.next()) {
				int id = rs.getInt("id"); 
				String question = rs.getString("question");
				String kind = rs.getString("kind");
				questions.add(new Question(id, s.getId(), question, kind, null));
			}
			//Add loaded questions for the actual survey
			s.setQuestions(questions);
		}
		disconnect();
		
		connect();
		//Load the existent answers for each question of each survey
		for (Survey s: surveys) {
			for(Question q: s.getQuestions()) {
				ArrayList<Answer> answers = new ArrayList<Answer>();
				connect();
				String QUERY_ANSWERS = "SELECT * FROM answers where idQuestion="+q.getId()+"";
				rs = getQuery(QUERY_ANSWERS);	
				while (rs.next()) {
					int id = rs.getInt("id");
					String answer = rs.getString("answer");
					answers.add(new Answer(id, q.getId(), answer));
				}
				//Add loaded answers for the actual question
				q.setAnswers(answers);
			}
		}
		disconnect();
		
		return surveys;
	}
	
	public static Survey loadSurvey(int idSurvey) throws Exception {
		//Loads an survey and it's returned as Survey object
		connect();
		String QUERY = "SELECT * FROM surveys WHERE id='"+idSurvey+"'";
		ResultSet rs = getQuery(QUERY);
		if (rs.next()) {			
			int idOwner = rs.getInt("idOwner");
			String subject = rs.getString("subject");
			int idCategory = rs.getInt("idCategory");
			Date creationDate = rs.getDate("creationDate");
			Date dueDate = rs.getDate("dueDate");
			String kind = rs.getString("kind");
			boolean anonymous = rs.getBoolean("anonymous");
			Survey s = new Survey(idSurvey, idOwner, subject, idCategory, creationDate, dueDate, kind, anonymous, null);

			disconnect();
			
			//Load the questions for this Survey
			connect();
			ArrayList<Question> questions = new ArrayList<Question>();
			QUERY = "SELECT * FROM questions WHERE idSurvey='"+idSurvey+"'";
			rs = getQuery(QUERY);
			while (rs.next()) {
				int id = rs.getInt("id"); 
				String question = rs.getString("question");
				String questionKind = rs.getString("kind");
				questions.add(new Question(id, idSurvey, question, questionKind, null));
			}
			disconnect();
			//Add the loaded questions to the Survey
			s.setQuestions(questions);
			
			//Load the answers for each question of the survey
			connect();
			for (Question q: s.getQuestions()) {
				ArrayList<Answer> answers = new ArrayList<Answer>();
				QUERY = "SELECT * FROM answers WHERE idQuestion='"+q.getId()+"'";
				rs = getQuery(QUERY);
				while (rs.next()) {
					int id = rs.getInt("id");
					String answer = rs.getString("answer");
					answers.add(new Answer(id, q.getId(), answer));
				}
				//Add the loaded answers for the actual question
				q.setAnswers(answers);
			}
			disconnect();
			
			return s;
		} else {
			disconnect();
			return null;
		}
	}
	
	public static int deleteSurvey(int idSurvey) throws Exception {
		//Delete the survey that corresponds to the inserted id by parameter and return
		//the number of rows deleted
		int cont = 0;

		connect();

		final String Delete_SQL = "DELETE FROM surveys WHERE id=" + idSurvey;

		Statement st = conn.createStatement();

		cont = st.executeUpdate(Delete_SQL);
		
		disconnect();
		
		return cont;
	}
	
	public static ArrayList<User> loadUsers() throws Exception {
		//Loads the existent categories and their's returned as String array
		ArrayList<User> users = new ArrayList<User>();
		connect();
		String QUERY = "SELECT * FROM users";
		ResultSet rs = getQuery(QUERY);	
		while (rs.next()) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String surname = rs.getString("surname");
			String login = rs.getString("login");
			String password = rs.getString("password");
			String email = rs.getString("email");
			boolean emailVerified = rs.getBoolean("emailVerified");
			String secretQuestion = rs.getString("secretQuestion");
			String secretAnswer = rs.getString("secretAnswer");
			boolean admin = rs.getBoolean("admin");
			users.add(new User(id,name,surname,login,password,email,emailVerified,secretQuestion,secretAnswer,admin));
		}
		disconnect();
		return users;
	}
	
	public static int deleteUser(int idUser) throws Exception {
		//Delete the user that corresponds to the inserted id by parameter and return
		//the number of rows deleted
		int cont = 0;

		connect();

		final String Delete_SQL = "DELETE FROM users WHERE id=" + idUser;

		Statement st = conn.createStatement();

		cont = st.executeUpdate(Delete_SQL);

		disconnect();
		
		return cont;
	}
	
	public static int deleteCategory(int idCategory) throws Exception {
		//Delete the category that corresponds to the inserted id by parameter and return
		//the number of rows deleted
		int cont = 0;

		connect();

		final String Delete_SQL = "DELETE FROM categories WHERE id=" + idCategory;

		Statement st = conn.createStatement();

		cont = st.executeUpdate(Delete_SQL);

		disconnect();
		
		return cont;
	}
	
	public static boolean categoryIsReferenced(int idCategory) throws Exception {
		connect();
		ResultSet rs = getQuery("SELECT * FROM surveys WHERE idCategory='"+idCategory+"'");
		boolean isReferenced = rs.next();
		disconnect();
		
		return isReferenced;
	}
	
	public static void createCategory(Category c) throws Exception {
		// Used to save an user to the DB
		connect();
		final String INSERT_SQL = "INSERT INTO categories(name) values(?)";
		PreparedStatement ps = conn.prepareStatement(INSERT_SQL,Statement.RETURN_GENERATED_KEYS);
		boolean lastAutoCommitState = conn.getAutoCommit();
		conn.setAutoCommit(false);
		try {
			ps.setString(1, c.getName());
			ps.executeUpdate();
			c.setId(getDbId(ps));
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			conn.setAutoCommit(lastAutoCommitState);
		}
		disconnect();
	}
	
	public static boolean categoryNameExists(String categoryName) throws Exception{
		//Checks if a login is present on DB
		boolean ok = false;
		connect();
		ResultSet rs = getQuery("SELECT * FROM categories WHERE name='"+categoryName+"'");
		ok = rs.next();
		disconnect();
		return ok;
	}
	
	public static int modifyCategory(int idCategorySelec, String newCategoryName)
		throws Exception {
		//Modifiy the category that corresponds to the inserted id by parameter and return
		//the number of rows modified
		int cont = 0;

		connect();

		final String Modify_SQL = "UPDATE categories SET name='" + newCategoryName +"' WHERE id="+idCategorySelec;

		Statement st = conn.createStatement();

		cont = st.executeUpdate(Modify_SQL);
		
		disconnect();
		
		return cont;
	}
	
	public static int createVisitorIfNoExists(Visitor v) throws Exception {
		//Comprueba si un visitante existente en la BBDD y si no existe (debido a que
		//es la primera vez que accede a la aplicación) lo crea.
		int id = -1;
		boolean ok = false;
		connect();
		ResultSet rs = getQuery("SELECT * FROM visitors WHERE NIF='"+v.getNIF()+"'");
		ok = rs.next();
		if (! ok) {
			//Si el visitante no exisite en la BBDD, lo creamos
			final String INSERT_SQL = "INSERT INTO visitors(NIF, name) values(?,?)";
			PreparedStatement ps = conn.prepareStatement(INSERT_SQL,Statement.RETURN_GENERATED_KEYS);
			boolean lastAutoCommitState = conn.getAutoCommit();
			conn.setAutoCommit(false);
			try {
				ps.setString(1, v.getNIF());
				ps.setString(2, v.getName());
				ps.executeUpdate();
				v.setId(getDbId(ps));
				id = v.getId();
			} catch (Exception e) {
				conn.rollback();
				throw e;
			} finally {
				conn.setAutoCommit(lastAutoCommitState);
			}
			disconnect();
			return id;
		}
		disconnect();
		return id;
	}
	
	public static void updateVisitorName(Visitor v) throws Exception {
		//Método que actualiza el nombre de un visitante
		connect();
		String SENTENCE="UPDATE visitors SET name='"+v.getName()+"' WHERE NIF='"+v.getNIF()+"'";
		conn.createStatement().executeUpdate(SENTENCE);
		disconnect();
	}
	
	public static ArrayList<Survey> loadPublicSurveys(Date fechaActual, boolean anonymous, Category category) throws Exception {
		return loadSurveys(fechaActual, anonymous, category, SurveyKind.PUBLIC);
	}
	
	public static ArrayList<Survey> loadPrivateSurveys(Date fechaActual, boolean anonymous, Category category) throws Exception {
		return loadSurveys(fechaActual, anonymous, category, SurveyKind.PRIVATE);
	}
	
	private static ArrayList<Survey> loadSurveys(Date fechaActual, boolean anonymous, Category category, SurveyKind kindRequired) throws Exception {
		ArrayList<Survey> surveys = new ArrayList<Survey>();
		connect();
		String QUERY = "SELECT * FROM surveys WHERE dueDate>'"+fechaActual+"' AND anonymous="+anonymous+" AND kind='"+kindRequired.toString()+"' AND idCategory="+category.getId();
		ResultSet rs = getQuery(QUERY);	
		while (rs.next()) {
			int id = rs.getInt("id");
			int idOwner = rs.getInt("idOwner");
			String subject = rs.getString("subject");
			int idCategory = rs.getInt("idCategory");
			Date creationDate = rs.getDate("creationDate");
			Date dueDate = rs.getDate("dueDate");
			String kind = rs.getString("kind");
			
			surveys.add(new Survey(id, idOwner, subject, idCategory, creationDate, dueDate, kind, anonymous, null));
		}
		disconnect();
		
		connect();
		//Load the existent questions for each survey
		for (Survey s: surveys) {
			ArrayList<Question> questions = new ArrayList<Question>();
			connect();
			String QUERY_QUESTIONS = "SELECT * FROM questions where idSurvey="+s.getId()+"";
			rs = getQuery(QUERY_QUESTIONS);	
			while (rs.next()) {
				int id = rs.getInt("id"); 
				String question = rs.getString("question");
				String kind = rs.getString("kind");
				questions.add(new Question(id, s.getId(), question, kind, null));
			}
			//Add loaded questions for the actual survey
			s.setQuestions(questions);
		}
		disconnect();
		
		connect();
		//Load the existent answers for each question of each survey
		for (Survey s: surveys) {
			for(Question q: s.getQuestions()) {
				ArrayList<Answer> answers = new ArrayList<Answer>();
				connect();
				String QUERY_ANSWERS = "SELECT * FROM answers where idQuestion="+q.getId()+"";
				rs = getQuery(QUERY_ANSWERS);	
				while (rs.next()) {
					int id = rs.getInt("id");
					String answer = rs.getString("answer");
					answers.add(new Answer(id, q.getId(), answer));
				}
				//Add loaded answers for the actual question
				q.setAnswers(answers);
			}
		}
		disconnect();
		
		return surveys;
	}
	
	public static Visitor loadVisitor(String NIF) throws Exception {
		//Loads an visitor and it's returned as Visitor object
		connect();
		String QUERY = "SELECT * FROM visitors WHERE NIF='"+NIF+"'";
		ResultSet rs = getQuery(QUERY);
		rs.next();
		int id = rs.getInt("id");
		String name = rs.getString("name");
		disconnect();
		Visitor v = new Visitor(id, NIF, name);
		return v;
	}
	
	public static Visitor loadVisitor(int id) throws Exception {
		//Loads an visitor and it's returned as Visitor object
		connect();
		String QUERY = "SELECT * FROM visitors WHERE id='"+id+"'";
		ResultSet rs = getQuery(QUERY);
		rs.next();
		String NIF = rs.getString("NIF");
		String name = rs.getString("name");
		disconnect();
		Visitor v = new Visitor(id, NIF, name);
		return v;
	}
	
	public static boolean surveyIsResponsed(int idSurvey, int idVisitor) throws Exception {
		//Checks if a login is present on DB
		boolean ok = false;
		connect();
		ResultSet rs = getQuery("SELECT * FROM transactions WHERE idSurvey="+idSurvey+" AND idVisitor="+idVisitor+" AND completed!='"+new Date(0)+"'");
		ok = rs.next();
		disconnect();
		return ok;
	}
	
	public static boolean isUserHasAcces(int idVisitor, int idSurvey) throws Exception {
		//Checks if a visitor can access to a survey.
		boolean ok = false;
		connect();
		ResultSet rs = getQuery("SELECT * FROM transactions WHERE idSurvey="+idSurvey+" AND idVisitor="+idVisitor+" AND completed='"+new Date(0)+"'");
		ok = rs.next();
		disconnect();
		return ok; 
	}
	
	public static String getResponsedDate(int idSurvey, int idVisitor) throws Exception {
		//Return the date when a user that corresponds with 'idVisitor' answered the indicated survey
		connect();
		ResultSet rs = getQuery("SELECT * FROM transactions WHERE idSurvey="+idSurvey+" AND idVisitor="+idVisitor);
		rs.next();
		Date responsedDate = rs.getDate("completed");	
		disconnect();
		String[] values = responsedDate.toString().split("-");
		return values[2]+"/"+values[1]+"/"+values[0];
	}
	
	public static int numberUsersResponsed(int idSurvey) throws Exception {
		//Checks if a login is present on DB
		int numberUsersResponded = 0;
		connect();
		ResultSet rs = getQuery("SELECT * FROM transactions WHERE idSurvey="+idSurvey+" AND completed!='"+new Date(0)+"'");
		while (rs.next()) {
			numberUsersResponded++;
		}
		disconnect();
		return numberUsersResponded;
	}
	
	public static void surveyIsAnswered(int idVisitor, int idSurvey, Date fechaRespuesta) throws Exception {
		//Función encargada de marcar que una encuesta ha sido ya respondida por un usuario (para que no pueda
		//volver a responderla
		connect();
		String QUERY = "SELECT * FROM transactions WHERE idVisitor="+idVisitor+" AND idSurvey="+idSurvey+" AND completed='"+new Date(0)+"'";
		ResultSet rs = getQuery(QUERY);
		boolean encontrado = rs.next();
		disconnect();
		//si encontramos algún resultado quiere decir que se trata de una encuesta privada y, por lo tanto,
		//simplemente actualizamos el cmapo correspondiente de la tabla con la fecha de respuesta
		if (encontrado) {
			connect();
			String SENTENCE="UPDATE transactions SET completed='"+fechaRespuesta+"' WHERE idVisitor="+idVisitor+" AND idSurvey="+idSurvey;
			conn.createStatement().executeUpdate(SENTENCE);
			disconnect();
		} else {
			connect();
	
			String Insert_SQL = "INSERT INTO transactions VALUES(?, ?, ?)";
			PreparedStatement ps = conn.prepareStatement(Insert_SQL, Statement.RETURN_GENERATED_KEYS);
	
			ps.setInt(1, idVisitor);
			ps.setInt(2, idSurvey);
			ps.setDate(3, fechaRespuesta);
	
			ps.executeUpdate();
			
			disconnect();
		}
	}

	public static void createResult(Result r) throws Exception {
		// Used to save an result to the DB
		connect();

		int idVisitor = r.getIdVisitor();
		int idAnswer = r.getIdAnswer();
		String value = r.getValue();

		String Insert_SQL = "INSERT INTO results(idVisitor, idAnswer, resultValue) VALUES(?, ?, ?)";
		PreparedStatement ps = conn.prepareStatement(Insert_SQL, Statement.RETURN_GENERATED_KEYS);

		ps.setInt(1, idVisitor);
		ps.setInt(2, idAnswer);
		ps.setString(3, value);

		ps.executeUpdate();
		
		disconnect();
	}
	
	public static int numberSurveys(int idCategory) throws Exception {
		//Devuelve el número de encuesta referenciadas por al categoría introducida
		int numberSurveys = 0;
		connect();
		ResultSet rs = getQuery("SELECT * FROM surveys WHERE idCategory="+idCategory);
		while (rs.next()) {
			numberSurveys++;
		}
		disconnect();
		return numberSurveys;
	}
	
	public static ArrayList<Result> loadOpenAnswerResults(int idAnswer) throws Exception {
		//Devuelve el listado de valores de la respuesta con el identificador introducido
		//(usado para las respuestas del tipo abierta)
		connect();
		ArrayList<Result> answers = new ArrayList<Result>();
		ResultSet rs = getQuery("SELECT * FROM results WHERE idAnswer="+idAnswer);
		for (int i=0 ; rs.next() ; i++) {
			answers.add(new Result(rs.getInt("idVisitor"), idAnswer, rs.getString("resultValue")));
		}
		disconnect();
		return answers;
	}
	
	public static String lastResponseDate(int idSurvey) throws Exception {
		//Carga y devuelve la última fecha de respuesta de una encuesta
		connect();
		String QUERY = "SELECT max(completed) AS completed FROM transactions WHERE idSurvey="+idSurvey;
		ResultSet rs = getQuery(QUERY);
		rs.next();
		Date lastCompleted = rs.getDate("completed");
		disconnect();
		String[] values = lastCompleted.toString().split("-");
		return values[2]+"/"+values[1]+"/"+values[0];
	}
	
	public static Visitor loadVisitorCensus(String nif) throws Exception {
		//Comprueba si ya existente un visitante con el nif introducido. Si no existe, lo
		//crea y lo devuelve. Por el contrario, lo carga y lo devuelve.
		Visitor v = new Visitor(nif, "-");
		int idVisitor = createVisitorIfNoExists(v);
		if (idVisitor == -1) {
			connect();
			String QUERY = "SELECT * FROM visitors WHERE NIF='"+nif+"'";
			ResultSet rs = getQuery(QUERY);
			rs.next();
			int id = rs.getInt("id");
			String name = rs.getString("name");
			disconnect();
			v.setId(id);
			v.setName(name);
		}
		return v;
	}
	
	public static void createVisitorCensus(int idVisitor, int idSurvey) throws Exception {
		// Used to save an user to the DB
		connect();
		final String INSERT_SQL = "INSERT INTO transactions VALUES(?, ?, ?)";
		PreparedStatement ps = conn.prepareStatement(INSERT_SQL,Statement.RETURN_GENERATED_KEYS);
		boolean lastAutoCommitState = conn.getAutoCommit();
		conn.setAutoCommit(false);
		try {
			ps.setInt(1, idVisitor);
			ps.setInt(2, idSurvey);
			ps.setDate(3, new Date(0));
			ps.executeUpdate();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			conn.setAutoCommit(lastAutoCommitState);
		}
		disconnect();
	}
}
